﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Data.SqlTypes;
using System.Management;
using System.Management.Instrumentation;


using AG.Utilities;



namespace SQLServerHealthCheck.SQLServer
{

    public class CollectServerAndServiceData
    {

        public static object thisLock = new object();

        List<Exception> errors;
        HealthCheckDataContext dc;
        SQLServerInstance sqlServerInstance;


        public List<Exception> ClearAll()
        {

            errors = new List<Exception>();
            try
            {

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());
                dc.ClearAll();
               
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);
            }

            return errors;
        }
        public List<Exception> Collect(SQLServerInstance SqlServerInstance)
        {
            errors = new List<Exception>();
            try
            {
                lock (thisLock)
                {

                    sqlServerInstance = SqlServerInstance;

                    bool newServer = false;
                    bool newService = false;

                    dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());


                    //Check if server allready exists
                    if (IsSQLServerAccessible(sqlServerInstance.GetConnectionString("Master")))
                    {

                        String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                        String serverName = parts[0];

                        Server server;
                        Service service;

                        if (dc.Servers.Count(s => s.Name.ToLower() == serverName.ToLower()) == 0)
                        {
                            server = new Server();
                            server.Name = serverName;
                            dc.Servers.InsertOnSubmit(server);

                            service = new Service();
                            service.Name = parts.Length == 2 ? parts[1] : "";
                            service.ServiceTypeID = (int)WMI.ManagedServiceType.SqlServer;
                            newService = true;
                            newServer = true;
                        }
                        else
                        {
                            server = dc.Servers.First(s => s.Name.ToLower() == serverName.ToLower());

                            service = server.Services.FirstOrDefault(s2 => s2.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());

                            if (service == null)
                            {
                                service = new Service();
                                service.Name = parts.Length == 2 ? parts[1] : "";
                                service.ServiceTypeID = (int)WMI.ManagedServiceType.SqlServer;
                                newService = true;
                            }
                        }

                        service.Authentication = SqlServerInstance.Authentication;
                        if (SqlServerInstance.Authentication.Contains("SQL"))
                        {
                            service.Login = SqlServerInstance.Login;
                            service.Password = SqlServerInstance.Password;
                        }
                        if (!String.IsNullOrEmpty(SqlServerInstance.Port))
                        {
                            service.Port = int.Parse(SqlServerInstance.Port);
                        }

                        if (newService)
                        {
                            service.Server = server;
                            dc.Services.InsertOnSubmit(service);
                        }

                        dc.SubmitChanges();


                        if (newServer)
                        {
                            CollectServerInfo(server);
                            CollectServerDrives(server);//Capture the details about the drive and free space
                            CollectServices(server);//Capture the details about different SQL sevices running in the servers
                            CollectErrors(server);
                            CollectPowerOptions(server);
                        }

                        CollectSQLServerServiceInfo(service);
                        CollectServiceSettings(); // Capture the Service Settings and Server Configurations
                        CollectLinkedServers();
                    }
                }
            }

            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);
            }


            return errors;
        }


        private void CollectServerDrives(Server server)
        {
            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " FillServerDrives method in Installation Check Started....");

                if (server.Services.Count(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer) > 0)
                {

                    Service sqlService = server.Services.First(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer);

                    if (IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                    {
                        using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                        {
                            cn.Open();
                            String sql = @"


	        use master
	
	        DECLARE @OleAutomationSetting int
	
	        SET @OleAutomationSetting =(SELECT CAST(value_in_use as int) FROM SYS.configurations WHERE name='Ole Automation Procedures')
	
	        IF @OleAutomationSetting=0
	        BEGIN
		        exec sp_configure 'Ole Automation Procedures', 1;
		        reconfigure with override;
		
	        END

	        SET NOCOUNT ON  
	        DECLARE @hr int  
	        DECLARE @fso int  
	        DECLARE @drive char(1)  
	        DECLARE @odrive int  
	        DECLARE @TotalSize varchar(20)  
	        DECLARE @MB bigint ; 
	        SET @MB = 1048576  

	        CREATE TABLE #drives (  drive char(1) PRIMARY KEY,  FreeSpace int NULL,  TotalSize int NULL)  

	        INSERT #drives(drive,FreeSpace) 
	        EXEC master.dbo.xp_fixeddrives  

	        EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
	        IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso  

	        DECLARE dcur 
	        CURSOR LOCAL FAST_FORWARD  
	        FOR 
		        SELECT drive 
		        from #drives  ORDER by drive  
		        OPEN dcur  
	        FETCH NEXT FROM dcur INTO @drive  

	        WHILE @@FETCH_STATUS=0  
	        BEGIN  
		        EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive  
		        IF @hr <> 0 
			        EXEC sp_OAGetErrorInfo @fso  
		        EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT  
		
		        IF @hr <> 0 
			        EXEC sp_OAGetErrorInfo @odrive  
		        UPDATE #drives  
		        SET TotalSize=@TotalSize/@MB  WHERE drive=@drive  
		
		        FETCH NEXT FROM dcur INTO @drive  
	        END  

	        CLOSE dcur  
	        DEALLOCATE dcur  
	        EXEC @hr=sp_OADestroy @fso  

	        IF @hr <> 0 
		        EXEC sp_OAGetErrorInfo @fso  
	        SELECT drive,  FreeSpace/1024.0 as 'Free',  TotalSize/1024.0 as 'Total',  CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'  
	        FROM #drives  ORDER BY drive  
	        DROP TABLE #drives 

	        exec sp_configure 'Ole Automation Procedures', @OleAutomationSetting;
	        reconfigure with override;

        ";

                            SqlCommand cmd = new SqlCommand(sql, cn);
                            SqlDataReader rd = cmd.ExecuteReader();

                            while (rd.Read())
                            {
                                ServerDrive drive = new ServerDrive();

                                drive.Drive = rd.GetString(0).ToCharArray()[0];
                                drive.FreeSpaceInGB = rd.GetDecimal(1);
                                drive.TotalSpaceInGB = rd.GetDecimal(2);

                                server.ServerDrives.Add(drive);
                            }

                            rd.Close();
                        }

                        dc.SubmitChanges();

                        //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " FillServerDrives method in Installation Check Completed....");
                    }

                    else
                    {
                        //Add server name that has problem in connectivity
                    }
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " Some error occured in Installation Check.Please check exception file for details");
            }

        }

        private void CollectServices(Server server)
        {
            try
            {

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " FillServices method in Installation Check Started....");

                WMI.ManagedComputer comp = new WMI.ManagedComputer(server.Name);


                foreach (WMI.Service service in comp.Services)
                {

                    Service s = null;
                    if (service.Type == WMI.ManagedServiceType.SqlServer)
                    {
                        String[] splitArray = service.Name.ToLower().Split('$');

                        String instanceName = splitArray.Length == 2 ? splitArray[1] : "";

                        if (server.Services.Count(s2 => s2.Name.ToLower() == instanceName.ToLower()) > 0)
                            s = server.Services.First(s2 => s2.Name.ToLower() == instanceName.ToLower());
                        else
                        {
                            s = new Service();
                            s.Name = instanceName;
                            s.Server = server;
                        }

                    }

                    else
                    {
                        s = new Service();
                        s.Name = service.Name;
                        s.Server = server;
                    }

                    s.LogOnAs = service.ServiceAccount;
                    s.StartMode = service.StartMode.ToString();
                    s.ServiceTypeID = (int)service.Type;


                }

                dc.SubmitChanges();

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " FillServices method in Installation Check Completed....");
            }
            catch (Exception ex)
            {


                errors.Add(ex);
                Logger.Error(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " Some error occured in Installation Check.Please check exception file for details");
            }

        }

        private void CollectServerInfo(Server server)
        {
            try
            {

                if (server.Services.Count(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer) > 0)
                {

                    Service sqlService = server.Services.First(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer);

                    using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                    {
                        cn.Open();







                        String sql = @"

DECLARE
@nProcessorCount INT
,@vcPhysicalMemory INT
,@vcProcessorNameString VARCHAR(60),
@OSversion varchar(50),
@IsVirtual bit,
@oldvalue int;


set @oldvalue =  CONVERT(int, (select value from   sys.configurations
where name = 'xp_cmdshell'));
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

CREATE TABLE #tblStats 
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200),
)
create table #tmp
(rownumber int IDENTITY(1,1),
Details varchar(8000))




INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'ProcessorCount'
INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'PhysicalMemory'


SELECT @nProcessorCount = Internal_Value FROM #tblStats WHERE [Index] = 16
SELECT @vcPhysicalMemory = Internal_Value FROM #tblStats WHERE [Index] = 19
SELECT @IsVirtual = 0--virtual_machine_type from  SYS.dm_os_sys_info

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
N'ProcessorNameString',
@vcProcessorNameString OUTPUT,
N'no_output' 

SELECT 
@nProcessorCount AS ProcessorCount
,@vcProcessorNameString AS ProcessorDescription
, @vcPhysicalMemory, @IsVirtual

DROP TABLE #tblStats
drop table #tmp
EXEC sp_configure 'xp_cmdshell', @oldvalue

";

                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();

                        if (rd.Read())
                        {
                            server.ProcessorCount = rd.GetInt32(0);
                            server.ProcessorType = rd.GetString(1);
                            server.MemoryInGB = rd.GetInt32(2);
                            server.OSVersion = CollectOSVersion(server);
                            server.IsVirtual = rd.GetBoolean(3);
                        }

                        rd.Close();
                    }

                    dc.SubmitChanges();

                    //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerInfo: ", " FillServerInfo method in Installation Check Completed....");

                }
            }
            catch (Exception ex)
            {



                errors.Add(ex);
                Logger.Error(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerInfo: ", " Some error occured in Installation Check.Please check exception file for details");
            }


        }
        private void CollectErrors(Server server)
        {
            try
            {
                ConnectionOptions oConn = new ConnectionOptions();
                ManagementScope oMs = new ManagementScope(
                   "\\\\" + server.Name + "\\root\\cimv2", oConn);
                string datestring = DateTime.Now.AddDays(-7).ToString("yyyyMMddHHmmss") +".0+060";
                ObjectQuery query = new ObjectQuery("Select * from Win32_NTLogEvent Where LogFile = 'Application' And (Type ='Error' Or Type ='Critical') And TimeGenerated > '" + datestring + "'"); 
                ManagementObjectSearcher searcher = new ManagementObjectSearcher(oMs, query);
                foreach (ManagementObject queryObj in searcher.Get())
                {
                    EventLog eventLog = new EventLog();
                    eventLog.Category = queryObj["Category"] == null ? "unknown" : queryObj["Category"].ToString();
                    eventLog.CategoryString = queryObj["CategoryString"] == null ? "unknown" : queryObj["CategoryString"].ToString();
                    eventLog.ComputerName = queryObj["ComputerName"] == null ? "unknown" : queryObj["ComputerName"].ToString();
                    eventLog.EventCode = queryObj["EventCode"] == null ? "unknown" : queryObj["EventCode"].ToString();
                    eventLog.EventIdentifier = queryObj["EventIdentifier"] == null ? "unknown" : queryObj["EventIdentifier"].ToString();
                    eventLog.EventType = queryObj["EventType"] == null ? "unknown" : queryObj["EventType"].ToString();
                    eventLog.LogFile = queryObj["LogFile"] == null ? "unknown" : queryObj["LogFile"].ToString();
                    eventLog.Message = queryObj["Message"] == null ? "unknown" : queryObj["Message"].ToString();
                    eventLog.RecordNumber = Convert.ToInt32(queryObj["RecordNumber"]);
                    eventLog.Server = server;
                    eventLog.SourceName = queryObj["SourceName"] == null ? "unknown" : queryObj["SourceName"].ToString();
                    string tempstring = queryObj["TimeGenerated"].ToString();
                    tempstring = tempstring.Split('.')[0];
                    string formatString = "yyyyMMddHHmmss";
                    // string sample = "20100611221912";
                    DateTime dt = DateTime.ParseExact(tempstring, formatString, null);
                    eventLog.TimeGenerated = dt;
                    eventLog.Type = queryObj["Type"] == null ? "unknown" : queryObj["Type"].ToString();
                    eventLog.User = queryObj["User"] == null ? "unknown" : queryObj["User"].ToString();
                   // if (eventLog.TimeGenerated > DateTime.Today.AddDays(-7))
                   //     dc.EventLogs.InsertOnSubmit(eventLog);
                  //  else
                   //     break;
                    dc.SubmitChanges(); 
                    
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);


            }

        }
        private void CollectPowerOptions(Server server)
        {
            try
            {
                ConnectionOptions oConn = new ConnectionOptions();
                ManagementScope oMs = new ManagementScope(
                   "\\\\" + server.Name + "\\root\\cimv2\\power", oConn);
                
                ObjectQuery query = new ObjectQuery("Select * from Win32_PowerPlan");
                ManagementObjectSearcher searcher = new ManagementObjectSearcher(oMs, query);
                foreach (ManagementObject queryObj in searcher.Get())
                {
                    PowerPlan pp = new PowerPlan();
                    pp.Server = server;
                    pp.Name = queryObj["ElementName"] == null ? "unknown" : queryObj["ElementName"].ToString();
                    pp.isActive = queryObj["IsActive"] == null ? false : Convert.ToBoolean(queryObj["IsActive"]);
                    pp.Description = queryObj["Description"] == null ? "unknown" : queryObj["Description"].ToString();
                    dc.PowerPlans.InsertOnSubmit(pp);
                    dc.SubmitChanges();

                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);


            }

        }
        private string CollectOSVersion(Server server)
        {
            string result = "";
            try
            {
                ConnectionOptions oConn = new ConnectionOptions();
                ManagementScope oMs = new ManagementScope(
                   "\\\\" + server.Name + "\\root\\cimv2", oConn);

                ObjectQuery query = new ObjectQuery("Select Caption, CSDversion from Win32_OperatingSystem");
                ManagementObjectSearcher searcher = new ManagementObjectSearcher(oMs, query);
                
                foreach (ManagementObject queryObj in searcher.Get())
                {
                    result += queryObj["Caption"].ToString();
                    result += " ";
                    result += queryObj["CSDVersion"].ToString();

                }
                return result;
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
                return result;

            }
            
        }
        private void CollectSQLServerServiceInfo(Service service)
        {

            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Started....");


                using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                {

                    cn.Open();

                    


                    //Get product Version,Edition etc
                    String sql = @"
declare
@oldvalue int;
set @oldvalue =  CONVERT(int, (select value from   sys.configurations
where name = 'Ole Automation Procedures'));
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE



SELECT 
                                    cast(SERVERPROPERTY('Edition' ) as varchar(100)) As Edition,
                                    REPLACE( cast(SERVERPROPERTY('ProductVersion' )as varchar(13)), '.0.','.00.') AS ProductVersion,
                                    SERVERPROPERTY ('productlevel') as Productlevel,
                                    SERVERPROPERTY ('Collation') as Collation,
                                  --  sqlserver_start_time
(select create_date 
  from sys.databases where database_id=2)
  
                                   FROM 
                                    sys.dm_os_sys_info

EXEC sp_configure 'Ole Automation Procedures', @oldvalue
RECONFIGURE;


"
                        ;
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    SqlDataReader rd = cmd.ExecuteReader();

                    if (rd.Read())
                    {

                        service.ProductVersion = rd["ProductVersion"].ToString();
                        service.Edition = rd["Edition"].ToString();
                        service.Collation = rd["Collation"].ToString();
                        //service.Description = dc.GetDescriptionFromProductVersion(service.ProductVersion);
                        service.Description = rd["Productlevel"].ToString();
                        service.Started = rd.GetDateTime(4);
                    }

                    rd.Close();

                }

                dc.SubmitChanges();

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Completed....");

            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " Some error occured in Installation Check.Please check exception file for details");

            }


        }

        private void CollectServiceSettings()
        {
            try
            {
                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName.ToLower());

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());
                    if (service != null)
                    {
                        //TODO: Use the get connection String method from the SQLServerInstance Class
                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @" SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
                                                   BEGIN TRY 

                                                        CREATE TABLE #TMPConfigs
                                                        (
  
                                                        [Name] VARCHAR(100),
                                                        [Value] sql_variant ,
                                                        [Minimum] sql_variant,
                                                        [Maximum] sql_variant

                                                        )
														
                                                        INSERT INTO #TMPConfigs
                                                        SELECT [Name],[Value],[Minimum],[Maximum] 
                                                        FROM sys.configurations
														
														INSERT INTO #TMPConfigs
                                                        SELECT 'MixedModeEnabled',SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Value],null,null
                                                        
                                                        SELECT *
                                                        FROM #TMPConfigs
                                                       
                                                        DROP TABLE #TMPConfigs
                                                       
                                                        END TRY
                                                        BEGIN CATCH
                                                        END CATCH;

COMMIT TRANSACTION;

                                            ";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();


                                while (rd.Read())
                                {
                                    ServiceSetting ServSet = new ServiceSetting();

                                    ServSet.Name = rd[0].ToString();

                                    object varobj = rd.GetSqlValue(1);

                                    if (varobj != null)
                                    {

                                        ServSet.Value = varobj;



                                        if (!rd.IsDBNull(2))
                                        {

                                            ServSet.Minimum = rd.GetInt32(2);

                                        }
                                        else
                                        {
                                            ServSet.Minimum = null;
                                        }



                                        if (!rd.IsDBNull(3))
                                        {
                                            ServSet.Maximum = rd.GetInt32(3);

                                        }
                                        else
                                        {
                                            ServSet.Maximum = null;
                                        }

                                        service.ServiceSettings.Add(ServSet);
                                    }
                                }

                                rd.Close();
                            }

                            dc.SubmitChanges();
                        }

                        else
                        {
                            //Add server name that has problem in connectivity
                        }
                    }
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " Some error occured in Installation Check.Please check exception file for details");
            }
        }


        private void CollectLinkedServers()
        {
            try
            {
                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName.ToLower());

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());
                    if (service != null)
                    {
                        //TODO: Use the get connection String method from the SQLServerInstance Class
                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @" SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
                                               SELECT 
	                                                name
	                                                ,product
	                                                ,provider
	                                                ,data_source


                                                FROM 
	                                                sys.servers 
                                                WHERE 
	                                                is_linked = 1;
                 
                                             COMMIT TRANSACTION;
                                                ";





                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();


                                while (rd.Read())
                                {
                                    LinkedServer linkedserver = new LinkedServer();
                                    linkedserver.ServerName = rd[0].ToString();
                                    linkedserver.ServiceId = service.ServiceID;

                                    if (!rd.IsDBNull(1))
                                    {
                                        linkedserver.ServerType = rd[1].ToString();
                                    }
                                    if (!rd.IsDBNull(2))
                                    {
                                        linkedserver.Provider = rd[2].ToString();
                                    }

                                    service.LinkedServers.Add(linkedserver);

                                }

                                rd.Close();
                            }

                            dc.SubmitChanges();
                        }

                    }
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " Some error occured in Installation Check.Please check exception file for details");
            }
        }

        public static bool IsSQLServerAccessible(string strconn)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                try
                {
                    conn.Open();

                    if (conn.State == ConnectionState.Open)
                    {
                        Logger.Debug("Server Accessible, ConnString: " + strconn);
                        return true;
                    }

                }
                catch (Exception sqle1)
                {
                    Logger.Debug("Server not Accessible, ConnString: " + strconn + ", ErrorMessage: " + sqle1.Message);

                    return false;
                }
            }
            return false;
        }
    }
}
